package org.edgar.example.DB.Oracle;

import oracle.jdbc.OracleConnection;
import oracle.jdbc.OracleStruct;
import oracle.jdbc.OracleTypeMetaData;
import org.geotools.data.*;
import org.geotools.data.jdbc.FilterToSQLException;
import org.geotools.data.oracle.OracleDialect;
import org.geotools.data.oracle.OracleFilterToSQL;
import org.geotools.data.oracle.sdo.GeometryConverter;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.data.simple.SimpleFeatureSource;
import org.geotools.factory.CommonFactoryFinder;
import org.geotools.feature.SchemaException;
import org.geotools.filter.text.cql2.CQLException;
import org.geotools.filter.text.ecql.ECQL;
import org.junit.jupiter.api.Test;
import org.locationtech.jts.geom.Coordinate;
import org.locationtech.jts.geom.Geometry;
import org.locationtech.jts.geom.GeometryFactory;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.feature.simple.SimpleFeatureType;
import org.opengis.filter.Filter;
import org.opengis.filter.FilterFactory2;
import org.opengis.filter.spatial.DWithin;

import java.sql.*;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

/**
 * [ ECQL 字符串 ]
 *         ↓
 * [ GeoTools ECQL.toFilter() → Filter ]
 *         ↓
 * [ GeometryExtractor 提取 Geometry ]
 *         ↓                         ↓
 * [ OracleFilterToSQL → Oracle SQL where 片段 ]
 *                                   ↓
 * [ GeometryConverter → SDO_GEOMETRY 参数 ]
 *                                   ↓
 * [ JDBC 执行查询 ]
 *         ↓
 * [ Oracle 空间表结果 ]
 */
public class GeometryExtractorTest {

    // 修复后的连接代码应该包含正确的初始化
    static {
        // 强制设置GeoTools初始化参数
        System.setProperty("org.geotools.referencing.forceXY", "true");
        System.setProperty("org.geotools.jdbc.isJNDIAvailable", "false");
    }


    /**
     * 测试 Oracle Datastore 数据库的空间查询
     */
    @Test
    public void test_JDBC_Datastore() {
        try {


            // info
            Map<String, Object> params = new HashMap<>();

            params.put("dbtype", "oracle");
            params.put("host", "127.0.0.1");
            params.put("port", 1521);
            params.put("database", "orcl2"); // 如果这是 SID
            params.put("user", "newuser");
            params.put("passwd", "newuser");
            params.put("schema", "NEWUSER"); // 可选

            for (Iterator<DataStoreFactorySpi> it = DataStoreFinder.getAvailableDataStores(); it.hasNext(); ) {
                DataStoreFactorySpi factory = it.next();
                System.out.println("可用的 DataStore 工厂: " + factory.getDisplayName());
            }

            for (Iterator<DataStoreFactorySpi> it = DataStoreFinder.getAvailableDataStores(); it.hasNext(); ) {
                DataStoreFactorySpi factory = it.next();
                System.out.println("尝试工厂: " + factory.getDisplayName());
                if (factory.canProcess(params)) {
                    System.out.println("✅ 找到匹配的工厂: " + factory.getDisplayName());
                    DataStore ds = factory.createDataStore(params);
                    if (ds != null) {
                        System.out.println("✅ 成功创建 DataStore: " + ds);
                    } else {
                        System.out.println("❌ 创建 DataStore 失败");
                    }
                }
            }

            // 1. 连接数据库
            DataStore dataStore = DataStoreFinder.getDataStore(params);
            if (dataStore == null) {
                throw new RuntimeException("无法连接到OracleSpatial数据库");
            }

//            // 2. 读取数据
//            String typeName = "SPATIAL_TABLE"; // 替换为实际的表名
//            SimpleFeatureSource featureSource = dataStore.getFeatureSource(typeName);
//
//
//            // 示例: 执行空间查询
//            String ecql = "INTERSECTS(geometry, POLYGON ((10 30, 20 30, 20 40, 10 40, 10 30)))";
//            // 示例: 执行属性+空间组合查询
////            String ecql = "NAME = 'Beijing' AND DWITHIN(geometry, POINT(116.4 39.9), 10, kilometers)";
//
//            // 解析ECQL为Filter对象
//            Filter filter = ECQL.toFilter(ecql);
//            // 创建Query对象
//            Query query = new Query(featureSource.getSchema().getTypeName(), filter);
//            // 执行查询
//            SimpleFeatureCollection result = featureSource.getFeatures(query);
//            // 输出结果
//            try (SimpleFeatureIterator iterator = result.features()) {
//                while (iterator.hasNext()) {
//                    SimpleFeature feature = iterator.next();
//                    System.out.println("Feature: " + feature.getID() + ", Geometry: " + feature.getDefaultGeometry());
//                }
//            }

        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 使用JDBC的方式链接 oracle 数据库
     */
    @Test
    public void test_conn_JDBC() {
        // database conn
        String URL = "jdbc:oracle:thin:@//127.0.0.1:1521/ORCLPDB";
        String USER = "newuser";
        String PASS = "newuser";
        Connection conn = null;
        try {
            // 1. 注册驱动 (JDBC 4.0+ 自动注册，可省略)
            Class.forName("oracle.jdbc.OracleDriver");

            // 2. 获取连接
            conn = DriverManager.getConnection(URL, USER, PASS);

            String strSQL = "select 1 from dual";
            // 3. 执行查询
            PreparedStatement stmt = conn.prepareStatement(strSQL);
            // 绑定参数并执行查询
//            stmt.setObject(1, sdo_geometry);
            ResultSet rs = stmt.executeQuery();
            if(rs.next()) {
                int anInt = rs.getInt(1);
                System.out.println(anInt);
            }


            // 构建 ESQL
            Filter filter = ECQL.toFilter("contains(SHAPE, POINT(10 -10))");

            GeometryExtractor extractor = new GeometryExtractor();
            filter.accept(extractor, null);
            // 构建 根据 filter 将空间数据提取出来
            Geometry geometry = extractor.getExtractedGeometry();

            // oracle 提供方法将 geometry 转为 SDO_GEOMETRY
            GeometryConverter geometryConverter = new GeometryConverter((OracleConnection) conn);
            //Used to convert double[] to SDO_ODINATE_ARRAY.
            OracleStruct sdo_geometry = geometryConverter.toSDO(geometry, 4326);

            System.out.println(sdo_geometry);



        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } catch (CQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 4. 关闭连接
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 使用JDBC的方式链接 oracle 数据库
     * 空间查询 属性查询 合并查询
     *          * -- 对应的数据库类型定义
     *          * CREATE TYPE MDSYS.SDO_GEOMETRY AS OBJECT (
     *          *   SDO_GTYPE NUMBER,     -- 几何类型（如2001=点）
     *          *   SDO_SRID  NUMBER,     -- 坐标系（如4326）
     *          *   SDO_POINT SDO_POINT_TYPE, -- 坐标值（仅点类型使用）
     *          *   SDO_ELEM_INFO SDO_ELEM_INFO_ARRAY, -- 复杂几何元素定义
     *          *   SDO_ORDINATES SDO_ORDINATE_ARRAY   -- 坐标序列
     *          * );
     *          *
     *          * SELECT * FROM SPATIAL_TABLE
     *          * WHERE SDO_RELATE(
     *          *     SHAPE,
     *          *     SDO_GEOMETRY(
     *          *         2001,  -- 点类型
     *          *         4326,  -- SRID
     *          *         SDO_POINT_TYPE(10, -10, NULL),
     *          *         NULL,
     *          *         NULL
     *          *     ),
     *          *     'mask=contains querytype=WINDOW'
     *          * ) = 'TRUE';
     *          *
     *          * // 查询有个致命弱点 之前发现过多 --------》》》》 改用SDO_FILTER先粗筛，再用SDO_RELATE精筛
     */
    @Test
    public void test_conn_JDBC_spatial_and_attr() {
        // database conn
        String URL = "jdbc:oracle:thin:@//127.0.0.1:1521/ORCLPDB";
        String USER = "newuser";
        String PASS = "newuser";
        Connection conn = null;
        try {
            // 1. 注册驱动 (JDBC 4.0+ 自动注册，可省略)
            Class.forName("oracle.jdbc.OracleDriver");

            // 2. 获取连接
            conn = DriverManager.getConnection(URL, USER, PASS);

            // 构建 ESQL
//            Filter filter = ECQL.toFilter("BBOX(GEOMETRY,116,39,117,40)"); //(116.404,39.915)
//            Filter filter = ECQL.toFilter("\"ID\" = 2"); //(116.404,39.915)
            Filter filter = ECQL.toFilter("BBOX(GEOMETRY,116,39,117,40) and \"ID\" = 2"); //(116.404,39.915)
            // 将filter 转化为 oracle 空间查询格式
            OracleFilterToSQL oracleFilterToSQL = new OracleFilterToSQL(null);
            String strOracleFilter = oracleFilterToSQL.encodeToString(filter);
            System.out.println("strOracleFilter = " +  strOracleFilter);

            String strSQL = "SELECT id, name FROM spatial_table " + strOracleFilter;
            System.out.println("query SQL = " + strSQL);
            // 查询
            // 3. 执行查询
            PreparedStatement stmt = conn.prepareStatement(strSQL);

            // 循环遍历取值 构建参数
            List<Object> values = oracleFilterToSQL.getLiteralValues();
            List<Class> types = oracleFilterToSQL.getLiteralTypes();
            GeometryConverter geometryConverter = new GeometryConverter((OracleConnection) conn);
            for (int i = 0; i < values.size(); i++) {
                Object val = values.get(i);
                Class<?> type = types.get(i);
                if (Geometry.class.isAssignableFrom(type)) {
                    OracleStruct sdoGeom = geometryConverter.toSDO((Geometry) val, 4326);
                    stmt.setObject(i + 1, sdoGeom);
                } else {
                    stmt.setObject(i + 1, val);
                }
            }
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                System.out.println("id: " + id + ", name: " + name);
            }


        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        } catch (CQLException e) {
            throw new RuntimeException(e);
        } catch (FilterToSQLException e) {
            throw new RuntimeException(e);
        } finally {
            // 4. 关闭连接
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
